﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Citi.ExpenseReport.Domain;
using Citi.ExpenseReport.WebUI.Models;

namespace Citi.ExpenseReport.WebUI.Util
{
    public enum ServiceIds
    {
        Application = 1,
        Email = 2,
        Desgold = 3,
        Telecom = 4,
        Communicator = 5,
        Workloud = 6,
        BlackBerry
    }

    public class ImportHelper
    {
        private static List<Employee> Employees { get; set; }
        private Employee employee = new Employee();

        public List<FeedModel> ImportAllFiles()
        {
            var errors = new List<ErrorModel>();

            var feeds = ImportApplication("", ref errors);
            feeds.AddRange(ImportEmail("", ref errors));

            return feeds;
        }

        public List<FeedModel> ImportApplication(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Application), pathFile, ServiceIds.Application, "Application", ref errors);

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportDesgold(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Desgold), pathFile, ServiceIds.Desgold, "Desgold", ref errors);

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportTelecom(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Telecom), pathFile, ServiceIds.Telecom, "Telecom", ref errors);

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportEmail(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Email), pathFile, ServiceIds.Email, "Email", ref errors, "Users", "Email");

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportCommunicator(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Communicator), pathFile, ServiceIds.Communicator, "Communicator", ref errors, null, "Communicator");

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportWorkloud(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.DesktopLaptop), pathFile, ServiceIds.Workloud, "Desktop & Laptop", ref errors, "Desktop e Laptop", "Desktop e Laptop");
            feeds.AddRange(ImportFile(new MappingColumnModel(MappingFileType.ThinClient), pathFile, ServiceIds.Workloud, "Thin Client", ref errors, "Thin Client", "Thin Client"));

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        public List<FeedModel> ImportBlackBerry(string pathFile, ref List<ErrorModel> errors)
        {
            var feeds = ImportFile(new MappingColumnModel(MappingFileType.Modem3GVivo), pathFile, ServiceIds.BlackBerry, "Modem 3G Vivo", ref errors, "Modem 3G Vivo", "Modem 3G Vivo");
            feeds.AddRange(ImportFile(new MappingColumnModel(MappingFileType.Modem3GClaro), pathFile, ServiceIds.BlackBerry, "Modem 3G Claro", ref errors, "Modem 3G Claro", "Modem 3G Claro"));

            SaveAll(feeds);

            return feeds.Select(FeedModel.CreateViewModel).ToList();
        }

        private void SaveAll(List<Feed> feeds)
        {
            feeds.ForEach(x=> x.Save(x));
        }

        private List<Feed> ImportFile(MappingColumnModel mappingColumn, string pathFile, ServiceIds serviceIds, string fileDescription, ref List<ErrorModel> errors,
                                      string worksheetName = null, string subServiceName = null)
        {
            DateTime pathDate;
            if (ValidateFile(pathFile, fileDescription, errors, out pathDate)) return null;

            //if (Employees == null) Employees = EmployeeRepository.SelectAllInDate(pathDate);
            if (Employees == null) Employees = employee.SelectAll<Employee>().ToList();

            using (var reader = new ExcelReader(pathFile))
            {
                var dataTable = worksheetName == null ? reader.GetData() : reader.GetData(worksheetName);
                var rows = dataTable.Rows.Cast<DataRow>().ToList();
                var subServices = new SubService().SelectByService((int)serviceIds);
                var feeds = new List<Feed>();
                var blankLine = 0;
                //new Feed().Delete(string.Format("Delete FROM Feed where SubServiceId in (Select Id from SubService where ServiceId = {0}) and Date = '{1}' ;",
                //    (int)serviceIds, pathDate));

                foreach (var row in rows)
                {
                    if (row.ItemArray.Count(x => string.IsNullOrEmpty(x.ToString())) == row.ItemArray.Count())
                    {
                        blankLine++;
                        if (blankLine >= 5) break;
                        continue;
                    }

                    var line = rows.IndexOf(row) + 2;
                    SubService subService = null;
                    Price price = null;

                    if (mappingColumn.SubServiceInColumns == null)
                    {
                        //var subServiceName = string.Empty;
                        if (string.IsNullOrEmpty(subServiceName)) subServiceName = row[mappingColumn.SubService].ToString();
                        subService = subServices.FirstOrDefault(s => s.Name == subServiceName);

                        if (subService == null)
                        {
                            errors.Add(new ErrorModel(line, string.Format(Message.SubServiceNotFound, subServiceName), fileDescription));
                            continue;
                        }

                        price = subService.Prices.OrderByDescending(x => x.Date).FirstOrDefault();
                        if (price == null && mappingColumn.UnitCost == -1 && !mappingColumn.FileHasPrice)
                        {
                            errors.Add(new ErrorModel(line, string.Format(Message.SubServiceHasNoPrice, subServiceName), fileDescription));
                            continue;
                        }
                    }

                    ErrorModel error;

                    if (mappingColumn.SubServiceInColumns != null)
                    {
                        feeds.AddRange(AddFeedInColumns(mappingColumn, row, pathDate, out error));
                        continue;
                    }

                    var feed = AddFeed(mappingColumn, row, pathDate, subService, price, out error);

                    if (feed == null)
                    {
                        error.Line = line;
                        error.File = fileDescription;
                        errors.Add(error);
                        continue;
                    }

                    //if (feed.UnitCost == 0 || Employees.All(e => e.SoeId != feed.SoeId)) continue;

                    feeds.Add(feed);
                }

                var feedHistory = new FeedHistory { ReferenceDate = GetDateFile(pathFile), ProcessedAt = DateTime.Now, ServiceId = (int)serviceIds };
                feedHistory.Save(feedHistory);

                return feeds;
            }
        }

        private static bool ValidateFile(string pathFile, string fileDescription, List<ErrorModel> errors, out DateTime pathDate)
        {
            pathDate = DateTime.MinValue;

            if (!File.Exists(pathFile))
            {
                errors.Add(new ErrorModel(0, string.Format(Message.FileNotExists, fileDescription, pathFile), fileDescription));
                return true;
            }

            if (Path.GetExtension(pathFile) != ".xls" && Path.GetExtension(pathFile) != ".xlsx")
            {
                errors.Add(new ErrorModel(0, string.Format(Message.InvalidFileExtension, fileDescription, pathFile), fileDescription));
                return true;
            }

            pathDate = GetDateFile(pathFile);

            if (pathDate == DateTime.MinValue)
            {
                errors.Add(new ErrorModel(0, string.Format(Message.InvalidFileName, fileDescription, pathFile), fileDescription));
                return true;
            }

            return false;
        }

        private Feed AddFeed(MappingColumnModel mappingColumn, DataRow row, DateTime pathDate, SubService subService, Price price, out ErrorModel errorModel)
        {

            if (ValidateFeed(mappingColumn, row, out errorModel)) return null;

            var feed = new Feed();

            try
            {
                feed.Date = pathDate;

                feed.Employee = mappingColumn.SoeId > -1 ? employee.GetBySoeid(row[mappingColumn.SoeId].ToString().ToUpper()) : null;
                feed.Amount = mappingColumn.Amount > -1 ? int.Parse(row[mappingColumn.Amount].ToString()) : 1;
                feed.Details = mappingColumn.Details > -1 ? row[mappingColumn.Details].ToString() : "-";
                feed.SubServiceId = subService.Id;
                feed.UnitCost = mappingColumn.UnitCost > -1 ? decimal.Parse(row[mappingColumn.UnitCost].ToString()) : price.UnitCost;

                return feed;
            }
            catch (Exception e)
            {
                errorModel = new ErrorModel(0, e.Message, string.Empty);
                return null;
            }
        }

        private static IEnumerable<Feed> AddFeedInColumns(MappingColumnModel mappingColumn, DataRow row, DateTime pathDate, out ErrorModel errorModel)
        {
            if (ValidateFeed(mappingColumn, row, out errorModel)) return null;

            return mappingColumn.SubServiceInColumns.Select(column => new Feed
            {
                Date = pathDate,
                Amount = 1,
                 Employee = new Employee().GetBySoeid(row[mappingColumn.SoeId].ToString().ToUpper()),
                Details = mappingColumn.Details > -1 ? row[mappingColumn.Details].ToString() : "-",
                UnitCost = decimal.Parse(row[column.Key].ToString()),
                SubServiceId = column.Value
            }).Where(feed => feed.UnitCost != 0).ToList();
        }

        private static bool ValidateFeed(MappingColumnModel mappingColumn, DataRow row, out ErrorModel errorModel)
        {
            int convertInt;
            decimal convertDecimal;
            var error = new StringBuilder();
            errorModel = null;

            if (mappingColumn.SoeId == -1)
                error.AppendLine(Message.SoeIdNotFound);
            if (mappingColumn.SoeId > -1 && string.IsNullOrEmpty(row[mappingColumn.SoeId].ToString()))
                error.AppendLine(string.Format(Message.SoeIdInvalid, row[mappingColumn.SoeId]));
            if (mappingColumn.Amount > -1 && !int.TryParse(row[mappingColumn.Amount].ToString(), out convertInt))
                error.AppendLine(string.Format(Message.AmountInvalid, row[mappingColumn.Amount]));
            if (mappingColumn.UnitCost > -1 && !decimal.TryParse(row[mappingColumn.UnitCost].ToString(), out convertDecimal))
                error.AppendLine(string.Format(Message.UnitCostInvalid, row[mappingColumn.UnitCost]));

            if (!string.IsNullOrEmpty(error.ToString()))
            {
                errorModel = new ErrorModel(0, error.ToString(), string.Empty);
                return true;
            }
            return false;
        }

        private static DateTime GetDateFile(string pathFile)
        {
            var pathWithExtension = pathFile.Replace(".xlsx", string.Empty).Replace(".xls", string.Empty);

            try
            {
                var date = pathWithExtension.Split(new[] { '_' }).Last();
                return new DateTime(int.Parse(date.Substring(0, 4)), int.Parse(date.Substring(4, 2)), 1);
            }
            catch (Exception)
            {
                return DateTime.MinValue;
            }
        }
    }
}

